﻿#include "databasepersistence.h"

namespace Common {

	const QString spacer = " ";

	QString parseMap(QMap<QString, QVariant> & maps)
	{
		QString result;
		QList<QString> keys = maps.keys();
		for (int i = 0; i < keys.size(); i++)
		{
			result += keys.at(i);
			result += " = ";

			QVariant val = maps.value(keys.at(i));

			switch (val.type())
			{
				case QVariant::Date:
				case QVariant::DateTime:
				case QVariant::String:
					result += (" '" + val.toString() + "' ");
					break;
				default:
					result += val.toString();
					break;
			}

			if (i < keys.size() - 1)
			{
				result += ", ";
			}
			else
			{
				result += " ";
			}
		}

		return result;
	}

	QString parseList(QList<QString> & selectKeys)
	{
		QString result;
		for (int i = 0; i < selectKeys.size(); i++)
		{
			result += selectKeys.at(i);
			if (i < selectKeys.size() - 1)
			{
				result += ",";
			}
		}
		return result;
	}

	Criteria::Criteria()
	{

	}

	Criteria & Criteria::add(Restrictions rest)
	{
		RestricitCriteriaType rc;
		rc.m_restrictis = rest;
		rc.m_criterialType = CAND;

		restricitinons.append(rc);

		return *this;

	}

	Criteria & Criteria::orr(Restrictions rest)
	{
		RestricitCriteriaType rc;
		rc.m_restrictis = rest;
		rc.m_criterialType = COR;

		restricitinons.append(rc);
		return *this;
	}


	QString Criteria::toSql()
	{
		QString result;
		QTextStream stream(&result, QIODevice::ReadWrite);

		for (int i = 0; i < restricitinons.size(); i++) {
			RestricitCriteriaType rc = restricitinons.at(i);

			if (i > 0)
			{
				switch (rc.m_criterialType)
				{
					case CAND: stream << spacer << "AND" << spacer; break;
					case COR: stream << spacer << "OR" << spacer; break;
					default:break;
				}
			}
			stream << rc.m_restrictis.toSql();
		}

		stream.flush();

		return result;
	}

	QString Criteria::toSql(QMap<QString, QString> tableAlias, bool isSupportAlias)
	{
		QString result;
		QTextStream stream(&result, QIODevice::ReadWrite);


		for (int i = 0; i < restricitinons.size(); i++) {
			RestricitCriteriaType rc = restricitinons.at(i);

			if (i > 0)
			{
				switch (rc.m_criterialType)
				{
					case CAND: stream << spacer << "AND" << spacer; break;
					case COR: stream << spacer << "OR" << spacer; break;
					default:break;
				}
			}

			if (isSupportAlias)
			{
				if (tableAlias.size() == 1)
				{
					stream << rc.m_restrictis.toSql(tableAlias.values().first(), isSupportAlias);
				}
				else
				{
					QString alias = tableAlias.value(rc.m_restrictis.tableName());
					stream << rc.m_restrictis.toSql(alias, isSupportAlias);
				}
			}
			else
			{
				if (tableAlias.size() == 1) {
					stream << rc.m_restrictis.toSql(tableAlias.values().first(), isSupportAlias);
				}
				else {
					stream << rc.m_restrictis.toSql(rc.m_restrictis.tableName(), isSupportAlias);
				}
			}
		}

		stream.flush();

		return result;
	}

	Restrictions::Restrictions(QString tName, QString key, QVariant value, OperateType type) :
		name(key), value(value), operation(type), tname(tName)
	{

	}

	Restrictions::Restrictions()
	{

	}

	Restrictions Restrictions::eq(QString tName, QString key, QVariant value)
	{
		return Restrictions(tName, key, value, OperateType::EQ);
	}

	Restrictions Restrictions::eq(QString key, QVariant value)
	{
		return Restrictions("", key, value, OperateType::EQ);
	}

	Restrictions Restrictions::gt(QString tName, QString key, QVariant value)
	{
		return Restrictions(tName, key, value, OperateType::GT);
	}

	Restrictions Restrictions::gt(QString key, QVariant value)
	{
		return Restrictions("", key, value, OperateType::GT);
	}

	Restrictions Restrictions::ge(QString tName, QString key, QVariant value)
	{
		return Restrictions(tName, key, value, OperateType::GE);
	}

	Restrictions Restrictions::ge(QString key, QVariant value)
	{
		return Restrictions("", key, value, OperateType::GE);
	}

	Restrictions Restrictions::lt(QString tName, QString key, QVariant value)
	{
		return Restrictions(tName, key, value, OperateType::LT);
	}

	Restrictions Restrictions::lt(QString key, QVariant value)
	{
		return Restrictions("", key, value, OperateType::LT);
	}

	Restrictions Restrictions::le(QString tName, QString key, QVariant value)
	{
		return Restrictions(tName, key, value, OperateType::LE);
	}

	Restrictions Restrictions::le(QString key, QVariant value)
	{
		return Restrictions("", key, value, OperateType::LE);
	}

	Restrictions Restrictions::ne(QString tName, QString key, QVariant value)
	{
		return Restrictions(tName, key, value, OperateType::NE);
	}

	Restrictions Restrictions::ne(QString key, QVariant value)
	{
		return Restrictions("", key, value, OperateType::NE);
	}

	Restrictions Restrictions::like(QString tName, QString key, QVariant value)
	{
		return Restrictions(tName, key, value, OperateType::LIKE);
	}

	Restrictions Restrictions::like(QString key, QVariant value)
	{
		return Restrictions("", key, value, OperateType::LIKE);
	}

	Restrictions Restrictions::in(QString tName, QString key, QVariant value)
	{
		return Restrictions(tName, key, value, OperateType::IN);
	}

	Restrictions Restrictions::in(QString key, QVariant value)
	{
		return Restrictions("", key, value, OperateType::IN);
	}

	bool Restrictions::operator<(const Restrictions & src)const
	{
		if (&src == this)
		{
			return false;
		}

		if (src.name == this->name && src.value == this->value)
		{
			return false;
		}

		return true;
	}

	QString Restrictions::toSql(QString tableAlais, bool isSupportAlias) const
	{
		QString result;
		QTextStream stream(&result, QIODevice::ReadWrite);

		if (isSupportAlias && tableAlais.size() > 0) {
			stream << spacer << tableAlais << spacer << "." << name << spacer;
		}
		else {
			stream << spacer << name << spacer;
		}

		switch (operation)
		{
			case EQ: stream << spacer << "=" << spacer; break;
			case GT: stream << spacer << ">" << spacer; break;
			case GE: stream << spacer << ">=" << spacer; break;
			case LT: stream << spacer << "<" << spacer; break;
			case LE: stream << spacer << "<=" << spacer; break;
			case NE: stream << spacer << "!=" << spacer; break;
			case LIKE: stream << spacer << "like" << spacer; break;
			case IN: stream << spacer << "in" << spacer; break;
			default:break;
		}

		switch (value.type())
		{
			case QVariant::Date:
			case QVariant::DateTime:
			case QVariant::String:
			case QVariant::ByteArray:
				stream << spacer << "'" << value.toString() << "'" << spacer;
				break;
			default:
				stream << value.toString();
				break;
		}

		stream.flush();

		return result;
	}

	RPersistence::RPersistence(const QString tableName) :
		tableName(tableName)
	{

	}

	RPersistence & RPersistence::insert(const QString key, QVariant value)
	{
		maps.insert(key, value);
		return *this;
	}

	/*!
		@warning 此种方式会造成QByteArray在申请空间时申请到之前未释放的空间，导致程序出错. \n
				 可使用其它重载方法代替。
	 */
	RPersistence &RPersistence::insert(std::vector<std::pair<QString, QVariant> > list)
	{
		std::for_each(list.begin(), list.end(), [&](const std::pair<QString, QVariant> & pair) {
			this->maps.insert(pair.first, pair.second);
		});
		return *this;
	}

	/*!
	 * @brief 对某些key添加占位符，其值由外部调用bindValue()来绑定
	 * @details 适用于blob等二进制对象，这些操作无法直接通过insert语句完成，需要配合QSqlQuery的addBindValue来实现。
	 * @attention 【20210606!!!】调用addBindValue()绑定参数时，其顺序一定要与placeholderKeys中参数一致
	 */
	RPersistence & RPersistence::addPlaceholders(std::vector<QString> placeholderKeys)
	{
		std::for_each(placeholderKeys.begin(), placeholderKeys.end(), [&](const QString key) {
			m_placeholderKeys.append(key);
		});
		return *this;
	}

	QString RPersistence::sql()
	{
		QString result;
		QTextStream stream(&result, QIODevice::ReadWrite);
		stream << "INSERT INTO" << spacer;

		stream << tableName;
		stream << spacer << "(" << spacer;

		QList<QString> keys = maps.keys();

		keys.append(m_placeholderKeys);

		result += parseList(keys);
		stream << spacer << ") VALUES (" << spacer;

		for (int i = 0; i < keys.size(); i++)
		{
			QString key = keys.at(i);
			
			//添加占位符，由外部绑定
			if (m_placeholderKeys.contains(key)) 
			{
				stream << spacer << "?" << spacer;
			}
			else
			{
				QVariant val = maps.value(key);

				switch (val.type())
				{
					case QVariant::Date:
					case QVariant::DateTime:
					case QVariant::String:
					case QVariant::ByteArray:
						stream << spacer << "'" << val.toString() << "'" << spacer;
						break;
					case QVariant::Char:
						stream << val.toChar();
						break;
					default:
						stream << val.toString();
						break;
				}
			}

			if (i < keys.size() - 1)
			{
				stream << "," << spacer;
			}
			else
			{
				stream << spacer << ")" << spacer;
			}
		}
		stream.flush();

		return result;
	}

	RUpdate::RUpdate(std::initializer_list<QString> tNames) :isAlias(true) {
		QString alias("T%1");
		int index = 0;
		for (auto p : tNames) {
			tableNames.insert(p, alias.arg(index++));
		}
	}

	RUpdate::RUpdate(const QString tName) :isAlias(true) {
		QString alias("T%1");
		tableNames.insert(tName, alias.arg(0));
	}

	RUpdate &RUpdate::enableAlias(bool flag)
	{
		isAlias = flag;
		return *this;
	}

	RUpdate & RUpdate::update(const QString tName, const QString key, QVariant value)
	{
		updateKeys.push_back(UpdateKeys{ tName,key,value });
		return *this;
	}

	RUpdate &RUpdate::update(const QString tName, std::vector<std::pair<QString, QVariant> > list)
	{
		std::for_each(list.begin(), list.end(), [&](const std::pair<QString, QVariant> & item) {
			this->updateKeys.push_back(UpdateKeys{ tName,item.first,item.second });
		});
		return *this;
	}

	RUpdate &RUpdate::on(const QString &tName1, const QString key1, const QString tName2, const QString value2)
	{
		onCondtions.push_back({ tName1,key1,tName2,value2 });
		return *this;
	}

	QString RUpdate::sql()
	{
		QString result;
		QTextStream stream(&result, QIODevice::ReadWrite);
		stream << "UPDATE" << spacer;

		if (tableNames.size() > 1) {
			for (int i = 0; i < tableNames.size(); i++) {
				stream << tableNames.keys().at(i);
				if (isAlias)
					stream << spacer << tableNames.value(tableNames.keys().at(i));
				if (i < tableNames.size() - 1) {
					stream << spacer << "LEFT JOIN" << spacer;
				}
			}
			stream << spacer << "ON" << spacer;
		}
		else {
			stream << tableNames.keys().at(0);
			if (isAlias)
				stream << spacer << tableNames.value(tableNames.keys().at(0)) << spacer;
		}

		if (onCondtions.size() > 0) {
			for (int i = 0; i < onCondtions.size(); i++) {
				OnContion condition = onCondtions.at(i);
				if (isAlias)
					stream << tableNames.value(condition.t1) << ".";
				stream << condition.v1 << "=";
				if (isAlias)
					stream << tableNames.value(condition.t2) << ".";
				stream << condition.v2;
				if (i < onCondtions.size() - 1) {
					stream << spacer << "AND" << spacer;
				}
			}
		}

		stream << spacer << "SET" << spacer;

		for (int i = 0; i < updateKeys.size(); i++)
		{
			const UpdateKeys & key = updateKeys.at(i);
			if (isAlias)
				stream << tableNames.value(key.tname) << ".";
			stream << key.tKey;
			stream << spacer << "=" << spacer;

			switch (key.value.type())
			{
				case QVariant::Date:
				case QVariant::DateTime:
				case QVariant::String:
				case QVariant::ByteArray:
					stream << spacer << "'" << key.value.toString() << "'" << spacer;
					break;
				default:
					stream << key.value.toString();
					break;
			}

			if (i < updateKeys.size() - 1) {
				stream << spacer << "," << spacer;
			}
			else {
				stream << spacer;
			}
		}

		if (ctia.size() > 0)
		{
			stream << spacer << "WHERE" << spacer;
			stream << ctia.toSql(tableNames, isAlias);
		}
		stream.flush();

		return result;
	}

	RSelect::RSelect(std::initializer_list<QString> tNames) :limitStart(-1), limitCount(-1), isSetLimit(false), isSetFunction(false)
	{
		QString alias("T%1");
		int index = 0;
		for (auto p : tNames) {
			tableNames.insert(p, alias.arg(index++));
		}
	}

	RSelect::RSelect(QString tName) :limitStart(-1), limitCount(-1), isSetLimit(false), isSetFunction(false)
	{
		QString alias("T%1");
		tableNames.insert(tName, alias.arg(0));
	}

	RSelect& RSelect::select(const QString &tName, std::initializer_list<QString> keys)
	{
		for (auto key : keys)
			selectedKeys.push_back(Keys{ tName,key });
		return *this;
	}

	RSelect & RSelect::select(const QString & tName)
	{
		return *this;
	}

	RSelect &RSelect::on(const QString &tName1, const QString key1, const QString tName2, const QString value2)
	{
		onCondtions.push_back({ tName1,key1,tName2,value2 });
		return *this;
	}

	bool RSelect::limit(unsigned int start, unsigned int count)
	{
		isSetLimit = false;
		if (start >= 0 && count > 0) {
			limitStart = start;
			limitCount = count;
			isSetLimit = true;
		}
		return isSetLimit;
	}

	RSelect & RSelect::groupBy(const QString & tName, const QString key)
	{
		groupAccords.push_back(Keys{ tName,key });
		return *this;
	}

	RSelect &RSelect::orderBy(const QString &tName, const QString key, SuperCondition::SOrder odr)
	{
		sortOrders.push_back(Orders{ tName,key,odr });
		return *this;
	}

    Common::RSelect & RSelect::avg(QString columnName)
	{
		isSetFunction = true;
		m_funcColumn = columnName;
		m_functionType = F_Avg;
		return *this;
	}

    Common::RSelect & RSelect::count(QString columnName /*= "*"*/, bool distinct /*= false*/)
	{
		isSetFunction = true;
		m_funcColumn = columnName;
		m_functionType = F_Count;
		isDistinct = distinct;
		return *this;
	}

    Common::RSelect & RSelect::first(QString columnName)
	{
		isSetFunction = true;
		m_funcColumn = columnName;
		m_functionType = F_First;
		return *this;
	}

    Common::RSelect & RSelect::last(QString columnName)
	{
		isSetFunction = true;
		m_funcColumn = columnName;
		m_functionType = F_Last;
		return *this;
	}

    Common::RSelect & RSelect::_max(QString columnName)
	{
		isSetFunction = true;
		m_funcColumn = columnName;
		m_functionType = F_Max;
		return *this;
	}

    Common::RSelect & RSelect::_min(QString columnName)
	{
		isSetFunction = true;
		m_funcColumn = columnName;
		m_functionType = F_Min;
		return *this;
	}

    Common::RSelect & RSelect::sum(QString columnName)
	{
		isSetFunction = true;
		m_funcColumn = columnName;
		m_functionType = F_Sum;
		return *this;
	}

    Common::RSelect & RSelect::var(QString columnName)
	{
		isSetFunction = true;
		m_funcColumn = columnName;
		m_functionType = F_Var;
		return *this;
	}

	QString RSelect::sql()
	{
		if (tableNames.size() == 0)
			return QString();

		QString result;
		QTextStream stream(&result, QIODevice::ReadWrite);
		stream << spacer << "SELECT" << spacer;

		if (isSetFunction) {
			switch (m_functionType)
			{
				case F_Avg:
					stream << "AVG(" << m_funcColumn << ") ";
					break;
				case F_Count:
					stream << "COUNT(";

					if (isDistinct)
						stream << "DISTINCT" << spacer;

					stream << m_funcColumn << ")";
					break;
				case F_First:
					stream << "FIRST(" << m_funcColumn << ")";
					break;
				case F_Last:
					stream << "LAST(" << m_funcColumn << ")";
					break;
				case F_Max:
					stream << "MAX(" << m_funcColumn << ")";
					break;
				case F_Min:
					stream << "MIN(" << m_funcColumn << ")";
					break;
				case F_Sum:
					stream << "SUM(" << m_funcColumn << ")";
					break;
				case F_Var:
					stream << "VAR(" << m_funcColumn << ")";
					break;
				default:
					break;
			}
		}
		else {
			if (selectedKeys.size() > 0) {
				for (int i = 0; i < selectedKeys.size(); i++) {
					stream << tableNames.value(selectedKeys.at(i).tname) << "." << selectedKeys.at(i).tkey;
					if (i != selectedKeys.size() - 1) {
						stream << spacer << "," << spacer;
					}
				}
			}
			else {
				stream << spacer << "*" << spacer;
			}
		}

		stream << spacer << "FROM" << spacer;

		if (tableNames.size() > 1) {
			for (int i = 0; i < tableNames.size(); i++) {
				stream << tableNames.keys().at(i) << spacer << tableNames.value(tableNames.keys().at(i));
				if (i < tableNames.size() - 1) {
					stream << spacer << "LEFT JOIN" << spacer;
				}
			}
			stream << spacer << "ON" << spacer;
		}
		else
			stream << tableNames.keys().at(0) << spacer << tableNames.value(tableNames.keys().at(0)) << spacer;

		if (onCondtions.size() > 0) {
			for (int i = 0; i < onCondtions.size(); i++) {
				OnContion condition = onCondtions.at(i);
				stream << tableNames.value(condition.t1) << "." << condition.v1 << "=" << tableNames.value(condition.t2) << "." << condition.v2;
				if (i < onCondtions.size() - 1) {
					stream << spacer << "AND" << spacer;
				}
			}
		}

		if (ctia.size() > 0)
		{
			stream << spacer << "WHERE" << spacer;
			stream << ctia.toSql(tableNames);
		}

		if (!isSetFunction && groupAccords.size() > 0)
		{
			stream << spacer << "GROUP BY" << spacer;
			for (int i = 0; i < groupAccords.size(); i++) {
				stream << tableNames.value(groupAccords.at(i).tname) << "." << groupAccords.at(i).tkey;
			}
		}

		if (!isSetFunction && sortOrders.size() > 0) {
			stream << spacer << "ORDER BY" << spacer;
			for (int i = 0; i < sortOrders.size(); i++) {
				stream << tableNames.value(sortOrders.at(i).tName) << "." << sortOrders.at(i).tkey << spacer << odrToString(sortOrders.at(i).odr);
			}
		}

		if (!isSetFunction && isSetLimit) {
			stream << spacer << "LIMIT" << spacer << limitStart << spacer + "," << spacer << limitCount;
		}

		stream.flush();

		return result;
	}

	void RSelect::clearFunctionQuery()
	{
		isSetFunction = false;
	}

	void RSelect::clear()
	{
		SuperCondition::clear();
		tableNames.clear();
		selectedKeys.clear();
		onCondtions.clear();
		sortOrders.clear();
		limitStart = -1;
		limitCount = -1;
		isSetLimit = false;
	}

	RDelete::RDelete(const QString tableName) :
		tableName(tableName)
	{

	}

	QString RDelete::sql()
	{
		QString result;
		QTextStream stream(&result, QIODevice::ReadWrite);
		stream << "DELETE FROM" << spacer;


		stream << tableName << spacer;

		if (ctia.size() > 0)
		{
			stream << spacer << "WHERE" << spacer;
			result += ctia.toSql();
		}
		return result;
	}

	void SuperCondition::clear()
	{
		ctia.clear();
	}

} //namespace Common
